--use PolePosition
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorInspVisitXMissionStructurePole_SelectForExcel')
	DROP PROCEDURE up_VendorInspVisitXMissionStructurePole_SelectForExcel

GO

CREATE PROCEDURE up_VendorInspVisitXMissionStructurePole_SelectForExcel
	 @Vendor_ProjectFK int 
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
	
AS

SET NOCOUNT ON	

SELECT 
	 Pole.[Vendor_InspVisit_XMissionStructure_PoleId] AS [Visit #]
	,Maps.[Name] AS [Map]
	,Pole.[SectionFk] AS [Section]
	,TopType.[Name] AS [Top Type]
	,kv.[Name] AS [Kv]
	,Pole.[Number]
	,InspectionType.[Name] AS [Inpection Type]
	,ResultType.[Name] AS [Result Type]
	,CASE WHEN Result.bElectrical = 1 THEN 'True' ELSE 'False' END AS [Electrical Problem]
	,Result.Comments AS [Comments]
	,dbo.fn_VendorXmissionInspection_GetNoteString(Result.Vendor_InspVisit_XmissionStructures_ResultId) AS [Notes]
	,Species.[Name] AS [Species]
	,Pole.[Length] AS [Length]
	,Class.[Name] AS [Class]
	,Treatment.[Name] AS [Treatment]
	,Pole.[Year] AS [Year]
	,Pole.GuardsQuantity AS [Guards Quantity]
	,YEAR(Pole.[LVisit]) AS [Last Visit]
	,Pole.[LInspector] AS [Last Visitor]
	,NULL AS [Last Visitor]
	,Vendors.[Name] AS [Vendor]	
	,Contact.[Name] AS [Inspector]	
	,Structure.[Date] AS [Acquisition Date]

FROM tb_Vendor_InspVisit_XmissionStructures_Poles Pole
LEFT JOIN tb_Vendor_InspVisit_XMissionStructures Structure 
	ON Pole.Vendor_InspVisit_XMissionStructureFk = Structure.Vendor_InspVisit_XMissionStructureId
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Results Result
	ON Result.Vendor_InspVisit_XMissionStructure_PoleFk = Pole.Vendor_InspVisit_XMissionStructure_PoleId
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes ResultType
	ON ResultType.Vendor_InspVisit_XmissionStructures_ResultTypeId = Result.Vendor_InspVisit_XmissionStructures_ResultTypeFk
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type InspectionType	
	ON InspectionType.Vendor_InspVisit_XmissionStructures_Inspection_TypeId = Result.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
LEFT JOIN tb_Utility_Polygons Maps
	ON Maps.Utility_PolygonId = Pole.[MapFk]
LEFT JOIN tb_Vendor_Contacts Contact
	ON Contact.Vendor_ContactId = Pole.Vendor_ContactFK
LEFT JOIN tb_Vendors Vendors 
	ON Vendors.VendorId = Contact.VendorFK
LEFT JOIN tb_Vendor_Visit_Type kv 
	ON kv.Vendor_Visit_TypeId = Pole.Vendor_Visit_TypeFK
LEFT JOIN tb_XMissionStructures_PoleTopTypes TopType
	ON TopType.XMissionStructures_PoleTopTypeId = Pole.TopTypeFk
LEFT JOIN tb_XMissionStructures_PoleSpecies Species
	ON Species.XMissionStructures_PoleSpeciesId = Pole.SpecieFk
LEFT JOIN tb_XMissionStructures_PoleTreatments Treatment
	ON Treatment.XMissionStructures_PoleTreatmentId = Pole.TreatmentFk
LEFT JOIN tb_XMissionStructures_PoleClasses Class
	ON Class.XMissionStructures_PoleClassId = Pole.ClassFk
WHERE
	Pole.Vendor_ProjectFK = @Vendor_ProjectFK

GO